

/**
	Author	    : Rohan
	Date	    : June 30th 2004
	Description : This procedure saves the address details in the Chiptrac Database. This is called
		      from with in the triggers on the table ARMASTER in the Epicore Database
	Usage       :  
			begin
			declare @address_id int
			exec sp_save_epicore_address '0015', 'address1', 'address2', 'address3', 'address4', 'city', 'state', 'country', 'zip', 'phone', 'fax', 'cont person', '1234', @address_id OUT
			print 'Address ID is ' + convert( char, @address_id )
			end
*/
alter proc sp_save_epicore_address
(
@cust_number    varchar(50),
@address1       varchar(50),
@address2       varchar(50),
@address3       varchar(50),
@address4       varchar(50),
@city           varchar(50),
@state          varchar(50),
@country        varchar(50),
@zip            varchar(50),
@phone          varchar(50),
@fax            varchar(50),
@contact_person varchar(50),
@ship_to_code   varchar(8),
@address_id     int OUT
)
as 
begin

declare @cust_id         int

select @cust_id = Customer_Id from Customer where C_Number = @cust_number	

--checking if the address is already existing
select @address_id = Address_ID from Address  (NOLOCK) where epicore_ship_code = @ship_to_code and ( exists( select 1 from Customer_Shipping_Address_Table (NOLOCK) where customer_id = @cust_id and address_id = address.address_id ) or exists ( select 1 from customer (NOLOCK) where customer_id = @cust_id and ( C_Default_Shipping_Address_ID = address.address_id or C_Default_Billing_Address_ID = address.address_id  ) ) ) 
if( @@rowcount = 0 )
begin
	--inserting the address details
	insert into address( Address_1, Address_2, City, State, Zipcode, Country, Phone_Number, Fax_Number, Contact_Person, Address_3, Address_4, Epicore_Ship_Code )
	            values ( @address1, @address2, @city, @state, @zip, @country, '', '', @contact_person, @address3, @address4, @ship_to_code )

	select @address_id = @@identity
		
	if( @ship_to_code != '' )
	begin
		if( @cust_id is not null )
		begin
			--if the customer details are already existing then insert the address mapping the table Customer_Shipping_Address_Table		 
			insert into Customer_Shipping_Address_Table( Customer_ID, Address_ID ) values ( @cust_id, @address_id )
		end
	end
		
	if( ( @contact_person is not null ) and ( @contact_person != '' ) )		
	begin
		--inserting the contact person  details
		insert into Address_Contact_Persons_Table( Address_ID, Contact_Person, Phone_Number, Cell_Number, Fax, Email, Address, Deactivated ) 
			                           values( @address_id, @contact_person, @phone, '', @fax, '', '', 0 )
	end
	
end
else
begin
	--updating the address details
	update address set Address_1         = @address1, 
			   Address_2         = @address2, 
			   City              = @city, 
                           State             = @state, 
			   Zipcode           = @zip, 
                           Country           = @country, 
			   Contact_Person    = @contact_person, 
			   Address_3         = @address3, 
			   Address_4         = @address4, 
			   Epicore_Ship_Code = @ship_to_code where Address_Id = @address_id
	
	
	if( ( @contact_person is not null ) and ( @contact_person != '' ) )		
	begin	
		--checking if the cotact person details are existing
		select @contact_person = Contact_Person from Address_Contact_Persons_Table where Address_ID = @address_id and Contact_Person = @contact_person
		if( @@rowcount = 0 )
		begin
			--inserting the contact person  details
			insert into Address_Contact_Persons_Table( Address_ID, Contact_Person, Phone_Number, Cell_Number, Fax, Email, Address, Deactivated ) 
			                           values( @address_id, @contact_person, @phone, '', @fax, '', '', 0 )		
		end
		else
		begin
			--updating the contact person  details
			update Address_Contact_Persons_Table set Phone_Number = @phone, Fax  = @fax where Address_ID = @address_id and Contact_Person = @contact_person
		end
	end

	--For billing address the phone and fax are of the customer and not of the billing address
	--So updating those details only when the address is not a billing address
	if( @ship_to_code != '' )
	begin
		update address set Phone_Number = @phone, Fax_Number = @fax where Address_ID = @address_id			
	end
end



end --End of Proc